package com.example.excelutildemo.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.excelutildemo.entiry.*;
import com.example.excelutildemo.handler.StockCellHandler;
import com.example.excelutildemo.handler.StockSheetHandler;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

@Service
public class WriteService {
    JdbcTemplate jdbcTemplate;

    public WriteService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    public <T> List<T> queryAll(String condition, Class<T> t) {
        return jdbcTemplate.query("select * from stock_date_week " + condition, new BeanPropertyRowMapper<>(t));
    }


    /**
     * 写到一个sheet页
     */
    public void writeToOneSheet() {
        List<StockDateWeek> stockDateWeeks1 = queryAll("limit 0,1000000", StockDateWeek.class);
        String fileName = "D:\\股票价格.xlsx";
        EasyExcel.write(fileName, StockDateWeek.class).sheet(0).doWrite(stockDateWeeks1);
    }


    /**
     * 根据(指定/排除)字段写入
     */
    public void writeByField() {
        Set<String> fieldNames = new HashSet<>();
        fieldNames.add("stockCode");
        fieldNames.add("stockName");
        fieldNames.add("openPrice");
        List<StockDateWeek> stockDateWeeks1 = queryAll("limit 0,60000", StockDateWeek.class);
        String fileName1 = "D:\\股票价格排除字段.xlsx";
        EasyExcel.write(fileName1, StockDateWeek.class).excludeColumnFieldNames(fieldNames).sheet("sheet1").doWrite(stockDateWeeks1);
        String fileName2 = "D:\\股票价格指定字段.xlsx";
        EasyExcel.write(fileName2, StockDateWeek.class).includeColumnFieldNames(fieldNames).sheet("sheet1").doWrite(stockDateWeeks1);
    }

    /**
     * 写入到指定列
     */
    public void writeByFieldSort() {
        List<StockDateWeek> stockDateWeeks1 = queryAll("limit 0,60000", StockDateWeek.class);
        String fileName1 = "D:\\股票价格根据字段排序.xlsx";
        EasyExcel.write(fileName1, StockDateWeek.class).sheet("sheet1").doWrite(stockDateWeeks1);
    }


    /**
     * 复杂表头写入
     */
    public void writeByComplexHead() {
        List<StockDateWeekComplexHead> stockDateWeeks1 = queryAll("limit 0,60000", StockDateWeekComplexHead.class);
        String fileName1 = "D:\\股票价格复杂表头.xlsx";
        EasyExcel.write(fileName1, StockDateWeekComplexHead.class).sheet("sheet1").doWrite(stockDateWeeks1);
    }

    /**
     * 写到一个sheet页
     */
    public void writeToMultiSheet() {
        String fileName = "D:\\股票价格多sheet.xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, StockDateWeek.class).build()) {
            List<StockDateWeek> stockDateWeeks1 = queryAll("limit 0,1000000", StockDateWeek.class);
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
            excelWriter.write(stockDateWeeks1, writeSheet);

            List<StockDateWeek> stockDateWeeks2 = queryAll("limit 1000001,2000000", StockDateWeek.class);
            WriteSheet writeSheet2 = EasyExcel.writerSheet("sheet2").build();
            excelWriter.write(stockDateWeeks2, writeSheet2);
        }
    }

    /**
     * 转换数据
     */
    public void writeByConvertData() {
        String fileName = "D:\\股票价格转换数据.xlsx";
        List<StockDateWeekConvert> stockDateWeeks = queryAll("limit 0,100000", StockDateWeekConvert.class);
        EasyExcel.write(fileName, StockDateWeekConvert.class).sheet(0).doWrite(stockDateWeeks);
    }

    /**
     * 列宽列高
     */
    public void writeByWidthAndHeightData() {
        String fileName = "D:\\股票价格自定义宽高.xlsx";
        List<StockDateWeekWidthAndHeight> stockDateWeeks = queryAll("limit 0,100000", StockDateWeekWidthAndHeight.class);
        EasyExcel.write(fileName, StockDateWeekWidthAndHeight.class).sheet(0).doWrite(stockDateWeeks);
    }

    /**
     * 合并单元格
     */
    public void writeByMergeCell() {
        String fileName = "D:\\股票价格合并单元格.xlsx";
        List<StockDateWeekMergeCell> stockDateWeeks = queryAll("limit 0,100000", StockDateWeekMergeCell.class);
        EasyExcel.write(fileName, StockDateWeekMergeCell.class).sheet(0).doWrite(stockDateWeeks);
    }

    /**
     * 单元格拦截器
     */
    public void writeByCellHandler() {
        String fileName = "D:\\股票价格单元格拦截器.xlsx";
        List<StockDateWeekMergeCell> stockDateWeeks = queryAll("limit 0,10000", StockDateWeekMergeCell.class);
        EasyExcel.write(fileName, StockDateWeekMergeCell.class).sheet(0).registerWriteHandler(new StockCellHandler()).registerWriteHandler(new StockSheetHandler()).doWrite(stockDateWeeks);
    }
}
